using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Transactions;
using PCSComProduct.Items.BO;
using PCSComProduct.Items.DS;
using PCSComSale.Order.DS;
using PCSComUtils.Common;
using PCSComUtils.Common.BO;
using PCSComUtils.DataAccess;
using PCSComUtils.DataContext;
using PCSComUtils.MasterSetup.DS;
using PCSComUtils.PCSExc;

namespace PCSComSale.Order.BO
{
    public class SaleOrderBO
    {
        private const string This = "PCSComSale.Order.BO.SaleOrderBO";

        public DataSet ListSaleType()
        {
            var dsSaleType = new SO_SaleTypeDS();
            return dsSaleType.List();
        }

        public DataSet ListGate()
        {
            var dsGate = new SO_GateDS();
            return dsGate.List();
        }

        public DataSet ListDiscountTerm()
        {
            var dsDiscoutTerm = new MST_DiscountTermDS();
            return dsDiscoutTerm.List();
        }

        public DataSet ListDeliveryTerm()
        {
            var dsDeliveryTerm = new MST_DeliveryTermDS();
            return dsDeliveryTerm.List();
        }

        public DataSet ListPaymentTerm()
        {
            var dsPaymentTerm = new MST_PaymentTermDS();
            return dsPaymentTerm.List();
        }

        public DataSet ListPause()
        {
            var dsPause = new MST_PauseDS();
            return dsPause.List();
        }

        public DataSet ListSaleOrderDetail(int? iSaleOrderMasterId)
        {
            var dsSaleOrderDetail = new SO_SaleOrderDetailDS();
            if (iSaleOrderMasterId != null)
            {
                var iId = (int) iSaleOrderMasterId;
                return dsSaleOrderDetail.List(iId);
            }
            return dsSaleOrderDetail.List();
        }

        public void Update(SO_SaleOrderMaster objMaster, DataSet pdstSODetail, List<int> deletedLines)
        {
            const string methodName = ".Update()";
            try
            {
                using (var trans = new TransactionScope())
                {
                    var dcPCS = new PCSDataContext(Utils.Instance.ConnectionString);
                    // update master object first
                    SO_SaleOrderMaster orderMaster = dcPCS.SO_SaleOrderMasters.SingleOrDefault(e => e.SaleOrderMasterID == objMaster.SaleOrderMasterID);
                    if (orderMaster != null)
                    {
                        orderMaster.BillToLocID = objMaster.BillToLocID;
                        orderMaster.BuyingLocID = objMaster.BuyingLocID;
                        orderMaster.CancelReasonID = objMaster.CancelReasonID;
                        orderMaster.CarrierID = objMaster.CarrierID;
                        orderMaster.CCNID = objMaster.CCNID;
                        orderMaster.Code = objMaster.Code;
                        orderMaster.CurrencyID = objMaster.CurrencyID;
                        orderMaster.CustomerPurchaseOrderNo = objMaster.CustomerPurchaseOrderNo;
                        orderMaster.DeliveryTermsID = objMaster.DeliveryTermsID;
                        orderMaster.DiscountTermsID = objMaster.DiscountTermsID;
                        orderMaster.ExchangeRate = objMaster.ExchangeRate;
                        orderMaster.ExportTax = objMaster.ExportTax;
                        orderMaster.ExportTaxRate = objMaster.ExportTaxRate;
                        orderMaster.FinishedDate = objMaster.FinishedDate;
                        orderMaster.LastChange = objMaster.LastChange;
                        orderMaster.LocationID = objMaster.LocationID;
                        orderMaster.PartyContactID = objMaster.PartyContactID;
                        orderMaster.PartyID = objMaster.PartyID;
                        orderMaster.PauseID = objMaster.PauseID;
                        orderMaster.PaymentMethodID = objMaster.PaymentMethodID;
                        orderMaster.PaymentTermsID = objMaster.PaymentTermsID;
                        orderMaster.Priority = objMaster.Priority;
                        orderMaster.SaleOrderMasterID = objMaster.SaleOrderMasterID;
                        orderMaster.SalesRepresentativeID = objMaster.SalesRepresentativeID;
                        orderMaster.SaleStatusID = objMaster.SaleStatusID;
                        orderMaster.SaleTypeID = objMaster.SaleTypeID;
                        orderMaster.ShipCompleted = objMaster.ShipCompleted;
                        orderMaster.ShipFromLocID = objMaster.ShipFromLocID;
                        orderMaster.ShipToLocID = objMaster.ShipToLocID;
                        orderMaster.SpecialTax = objMaster.SpecialTax;
                        orderMaster.SpecialTaxRate = objMaster.SpecialTaxRate;
                        orderMaster.TotalAmount = objMaster.TotalAmount;
                        orderMaster.TotalDiscountAmount = objMaster.TotalDiscountAmount;
                        orderMaster.TotalExportAmount = objMaster.TotalExportAmount;
                        orderMaster.TotalNetAmount = objMaster.TotalNetAmount;
                        orderMaster.TotalSpecialTaxAmount = objMaster.TotalSpecialTaxAmount;
                        orderMaster.TotalVATAmount = objMaster.TotalVATAmount;
                        orderMaster.TransDate = objMaster.TransDate;
                        orderMaster.TypeID = objMaster.TypeID;
                        orderMaster.UserName = objMaster.UserName;
                        orderMaster.VAT = objMaster.VAT;
                        orderMaster.VATRate = objMaster.VATRate;
                    }

                    #region update sale order details

                    foreach (DataRow dr in pdstSODetail.Tables[0].Rows)
                    {
                        if (dr.RowState == DataRowState.Deleted || dr.RowState == DataRowState.Detached)
                        {
                            continue;
                        }
                        int saleOrderDetailId = Convert.ToInt32(dr[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD]);
                        SO_SaleOrderDetail orderDetail = dcPCS.SO_SaleOrderDetails.SingleOrDefault(e => e.SaleOrderDetailID == saleOrderDetailId);
                        bool isNewOrder = false;
                        if (orderDetail == null)
                        {
                            orderDetail = new SO_SaleOrderDetail {SaleOrderDetailID = 0};
                            isNewOrder = true;
                        }
                        if (dr[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD] != null && dr[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.SaleOrderLine = Convert.ToInt32(dr[SO_SaleOrderDetailTable.SALEORDERLINE_FLD].ToString());
                        }
                        if (dr[SO_SaleOrderDetailTable.AUTOCOMMIT_FLD] != null && dr[SO_SaleOrderDetailTable.AUTOCOMMIT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.AutoCommit = Convert.ToBoolean(dr[SO_SaleOrderDetailTable.AUTOCOMMIT_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD] != null && dr[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.OrderQuantity = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.UNITPRICE_FLD] != null && dr[SO_SaleOrderDetailTable.UNITPRICE_FLD].ToString() != string.Empty)
                        {
                            orderDetail.UnitPrice = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.UNITPRICE_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.VATAMOUNT_FLD] != null && dr[SO_SaleOrderDetailTable.VATAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.VATAmount = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.VATAMOUNT_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD] != null && dr[SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ExportTaxAmount = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD] != null && dr[SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.SpecialTaxAmount = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD].ToString());
                        }
                        if (dr[SO_SaleOrderDetailTable.TOTALAMOUNT_FLD] != null && dr[SO_SaleOrderDetailTable.TOTALAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.TotalAmount = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.TOTALAMOUNT_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD] != null && dr[SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.DiscountAmount = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.NETAMOUNT_FLD] != null && dr[SO_SaleOrderDetailTable.NETAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.NetAmount = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.NETAMOUNT_FLD]);
                        }
                        orderDetail.ItemCustomerCode = dr[SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD].ToString();
                        orderDetail.ItemCustomerRevision = dr[SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD].ToString();
                        if (dr[SO_SaleOrderDetailTable.CANCELREASONID_FLD] != null && dr[SO_SaleOrderDetailTable.CANCELREASONID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.CancelReasonID = Convert.ToInt32(dr[SO_SaleOrderDetailTable.CANCELREASONID_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.VATPERCENT_FLD] != null && dr[SO_SaleOrderDetailTable.VATPERCENT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.VATPercent = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.VATPERCENT_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD] != null && dr[SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ExportTaxPercent = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD] != null && dr[SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.SpecialTaxPercent = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.UMRATE_FLD] != null && dr[SO_SaleOrderDetailTable.UMRATE_FLD].ToString() != string.Empty)
                        {
                            orderDetail.UMRate = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.UMRATE_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.SHIPQUANTITY_FLD] != null && dr[SO_SaleOrderDetailTable.SHIPQUANTITY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ShipQuantity = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.SHIPQUANTITY_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.BACKORDERQTY_FLD] != null && dr[SO_SaleOrderDetailTable.BACKORDERQTY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.BackOrderQty = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.BACKORDERQTY_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.STOCKQUANTITY_FLD] != null && dr[SO_SaleOrderDetailTable.STOCKQUANTITY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.StockQuantity = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.STOCKQUANTITY_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.PRODUCTID_FLD] != null && dr[SO_SaleOrderDetailTable.PRODUCTID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ProductID = Convert.ToInt32(dr[SO_SaleOrderDetailTable.PRODUCTID_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD] != null && dr[SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ConvertedQuantity = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.REASONID_FLD] != null && dr[SO_SaleOrderDetailTable.REASONID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ReasonID = Convert.ToInt32(dr[SO_SaleOrderDetailTable.REASONID_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.STOCKUMID_FLD] != null && dr[SO_SaleOrderDetailTable.STOCKUMID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.StockUMID = Convert.ToInt32(dr[SO_SaleOrderDetailTable.STOCKUMID_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.SELLINGUMID_FLD] != null && dr[SO_SaleOrderDetailTable.SELLINGUMID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.SellingUMID = Convert.ToInt32(dr[SO_SaleOrderDetailTable.SELLINGUMID_FLD]);
                        }
                        orderDetail.FGoodsCode = dr[SO_SaleOrderDetailTable.FGOODSCODE_FLD].ToString();
                        if (dr[SO_SaleOrderDetailTable.DUEDATE_FLD] != null && dr[SO_SaleOrderDetailTable.DUEDATE_FLD].ToString() != string.Empty)
                        {
                            orderDetail.DueDate = Convert.ToDateTime(dr[SO_SaleOrderDetailTable.DUEDATE_FLD]);
                        }
                        orderDetail.ItemDescription = dr[SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD].ToString();
                        if (dr[SO_SaleOrderDetailTable.WOQUANTITY_FLD] != null && dr[SO_SaleOrderDetailTable.WOQUANTITY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.WOQuantity = Convert.ToDecimal(dr[SO_SaleOrderDetailTable.WOQUANTITY_FLD]);
                        }
                        if (dr[SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD] != null && dr[SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ShipCompleted = Convert.ToBoolean(dr[SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD]);
                        }

                        // new order detail
                        if (isNewOrder)
                        {
                            // create delivery schedule for new order detail
                            orderDetail.SO_DeliverySchedules.Add(new SO_DeliverySchedule
                                                                     {
                                                                         Line = 1,
                                                                         DeliveryQuantity = orderDetail.OrderQuantity,
                                                                         ScheduleDate = orderDetail.DueDate
                                                                     });
                            orderDetail.SaleOrderMasterID = orderMaster.SaleOrderMasterID;
                            // add new detail to master order
                            orderMaster.SO_SaleOrderDetails.Add(orderDetail);
                        }
                    }

                    if (deletedLines.Count > 0)
                    {
                        var saleOrderLinesToDelete = dcPCS.SO_SaleOrderDetails.Where(so => deletedLines.Contains(so.SaleOrderDetailID));
                        dcPCS.SO_SaleOrderDetails.DeleteAllOnSubmit(saleOrderLinesToDelete);
                    }

                    #endregion

                    // submit all changes
                    dcPCS.SubmitChanges();
                    trans.Complete();
                }
            }
            catch (SqlException ex)
            {
                if (ex.Errors.Count > 1)
                {
                    if (ex.Number == ErrorCode.SQLDUPLICATE_KEYCODE)
                        throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex);
                    throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
                }
                throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
            }
        }

        public void DeleteSaleOrderDetail(int iSaleOrderDetailID)
        {
            using (var trans = new TransactionScope())
            {
                using (var dcPCS = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    //Delete Saleorder
                    dcPCS.DeleteSaleOrderDetail(iSaleOrderDetailID);
                    dcPCS.SubmitChanges();
                }
                // complete transaction
                trans.Complete();
            }
        }

        public void DeleteSaleOrders(int iSaleOrderID)
        {
            using (var trans = new TransactionScope())
            {
                using (var dcPCS = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    //Delete Saleorder
                    dcPCS.DeleteSaleOrder(iSaleOrderID);
                    dcPCS.SubmitChanges();
                }
                // complete transaction
                trans.Complete();
            }
        }

        public void UpdateSaleOrder(object pvoSOMaster, DataSet pdstSODetail)
        {
            var dsMaster = new SO_SaleOrderMasterDS();
            var voMaster = (SO_SaleOrderMasterVO) pvoSOMaster;
            dsMaster.Update(pvoSOMaster);
            if (pdstSODetail != null)
            {
                if (pdstSODetail.Tables.Count > 0)
                {
                    foreach (DataRow objRow in pdstSODetail.Tables[0].Rows)
                    {
                        if (objRow.RowState == DataRowState.Deleted) continue;
                        objRow[SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD] =
                            ((SO_SaleOrderMasterVO) pvoSOMaster).SaleOrderMasterID;
                    }
                    var dsDetail = new SO_SaleOrderDetailDS();
                    dsDetail.UpdateDataSet(pdstSODetail);
                    // Update DeliverySchedule
                    var deliveryScheduleDs = new SO_DeliveryScheduleDS();
                    DataSet dsODdetail = dsDetail.List(voMaster.SaleOrderMasterID);
                    DataSet dsDeliverySchedule = deliveryScheduleDs.List(voMaster.SaleOrderMasterID);

                    foreach (DataRow objRow in dsODdetail.Tables[0].Rows)
                    {
                        DataRow[] drowExist =
                            dsDeliverySchedule.Tables[0].Select(SO_DeliveryScheduleTable.SALEORDERDETAILID_FLD + "=" +
                                                                objRow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD]);
                        if (drowExist.Length == 0)
                        {
                            DataRow newrow = dsDeliverySchedule.Tables[0].NewRow();

                            newrow[SO_DeliveryScheduleTable.SALEORDERDETAILID_FLD] =
                                objRow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD];
                            newrow[SO_DeliveryScheduleTable.LINE_FLD] =
                                objRow[SO_SaleOrderDetailTable.SALEORDERLINE_FLD];
                            newrow[SO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD] =
                                objRow[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD];
                            newrow[SO_DeliveryScheduleTable.SCHEDULEDATE_FLD] =
                                objRow[SO_SaleOrderDetailTable.DUEDATE_FLD];
                            dsDeliverySchedule.Tables[0].Rows.Add(newrow);
                        }
                    }
                    DataTable dtbChanges = dsDeliverySchedule.Tables[0].GetChanges(DataRowState.Added);
                    if (dtbChanges != null && dtbChanges.Rows.Count > 0)
                        deliveryScheduleDs.UpdateDataSet(dsDeliverySchedule);
                }
            }
        }

        public int Insert(SO_SaleOrderMaster orderMaster, DataSet pdstSODetail)
        {
            const string methodName = "" + ".Insert()";
            try
            {
                using (var trans = new TransactionScope())
                {
                    var dcPCS = new PCSDataContext(Utils.Instance.ConnectionString);
                    foreach (DataRow detailRow in pdstSODetail.Tables[0].Rows)
                    {
                        var orderDetail = new SO_SaleOrderDetail();
                        if (detailRow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD] != null && detailRow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.SaleOrderLine = Convert.ToInt32(detailRow[SO_SaleOrderDetailTable.SALEORDERLINE_FLD].ToString());
                        }
                        if (detailRow[SO_SaleOrderDetailTable.AUTOCOMMIT_FLD] != null && detailRow[SO_SaleOrderDetailTable.AUTOCOMMIT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.AutoCommit = Convert.ToBoolean(detailRow[SO_SaleOrderDetailTable.AUTOCOMMIT_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD] != null && detailRow[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.OrderQuantity = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.UNITPRICE_FLD] != null && detailRow[SO_SaleOrderDetailTable.UNITPRICE_FLD].ToString() != string.Empty)
                        {
                            orderDetail.UnitPrice = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.UNITPRICE_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.VATAMOUNT_FLD] != null && detailRow[SO_SaleOrderDetailTable.VATAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.VATAmount = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.VATAMOUNT_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD] != null && detailRow[SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ExportTaxAmount = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.EXPORTTAXAMOUNT_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD] != null && detailRow[SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.SpecialTaxAmount = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.SPECIALTAXAMOUNT_FLD].ToString());
                        }
                        if (detailRow[SO_SaleOrderDetailTable.TOTALAMOUNT_FLD] != null && detailRow[SO_SaleOrderDetailTable.TOTALAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.TotalAmount = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.TOTALAMOUNT_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD] != null && detailRow[SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.DiscountAmount = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.DISCOUNTAMOUNT_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.NETAMOUNT_FLD] != null && detailRow[SO_SaleOrderDetailTable.NETAMOUNT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.NetAmount = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.NETAMOUNT_FLD]);
                        }
                        orderDetail.ItemCustomerCode = detailRow[SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD].ToString();
                        orderDetail.ItemCustomerRevision = detailRow[SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD].ToString();
                        if (detailRow[SO_SaleOrderDetailTable.CANCELREASONID_FLD] != null && detailRow[SO_SaleOrderDetailTable.CANCELREASONID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.CancelReasonID = Convert.ToInt32(detailRow[SO_SaleOrderDetailTable.CANCELREASONID_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.VATPERCENT_FLD] != null && detailRow[SO_SaleOrderDetailTable.VATPERCENT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.VATPercent = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.VATPERCENT_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD] != null && detailRow[SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ExportTaxPercent = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.EXPORTTAXPERCENT_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD] != null && detailRow[SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD].ToString() != string.Empty)
                        {
                            orderDetail.SpecialTaxPercent = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.SPECIALTAXPERCENT_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.UMRATE_FLD] != null && detailRow[SO_SaleOrderDetailTable.UMRATE_FLD].ToString() != string.Empty)
                        {
                            orderDetail.UMRate = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.UMRATE_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.SHIPQUANTITY_FLD] != null && detailRow[SO_SaleOrderDetailTable.SHIPQUANTITY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ShipQuantity = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.SHIPQUANTITY_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.BACKORDERQTY_FLD] != null && detailRow[SO_SaleOrderDetailTable.BACKORDERQTY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.BackOrderQty = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.BACKORDERQTY_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.STOCKQUANTITY_FLD] != null && detailRow[SO_SaleOrderDetailTable.STOCKQUANTITY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.StockQuantity = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.STOCKQUANTITY_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.PRODUCTID_FLD] != null && detailRow[SO_SaleOrderDetailTable.PRODUCTID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ProductID = Convert.ToInt32(detailRow[SO_SaleOrderDetailTable.PRODUCTID_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD] != null && detailRow[SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ConvertedQuantity = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.CONVERTEDQUANTITY_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.REASONID_FLD] != null && detailRow[SO_SaleOrderDetailTable.REASONID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ReasonID = Convert.ToInt32(detailRow[SO_SaleOrderDetailTable.REASONID_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.STOCKUMID_FLD] != null && detailRow[SO_SaleOrderDetailTable.STOCKUMID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.StockUMID = Convert.ToInt32(detailRow[SO_SaleOrderDetailTable.STOCKUMID_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.SELLINGUMID_FLD] != null && detailRow[SO_SaleOrderDetailTable.SELLINGUMID_FLD].ToString() != string.Empty)
                        {
                            orderDetail.SellingUMID = Convert.ToInt32(detailRow[SO_SaleOrderDetailTable.SELLINGUMID_FLD]);
                        }
                        orderDetail.FGoodsCode = detailRow[SO_SaleOrderDetailTable.FGOODSCODE_FLD].ToString();
                        if (detailRow[SO_SaleOrderDetailTable.DUEDATE_FLD] != null && detailRow[SO_SaleOrderDetailTable.DUEDATE_FLD].ToString() != string.Empty)
                        {
                            orderDetail.DueDate = Convert.ToDateTime(detailRow[SO_SaleOrderDetailTable.DUEDATE_FLD]);
                        }
                        orderDetail.ItemDescription = detailRow[SO_SaleOrderDetailTable.ITEMDESCRIPTION_FLD].ToString();
                        if (detailRow[SO_SaleOrderDetailTable.WOQUANTITY_FLD] != null && detailRow[SO_SaleOrderDetailTable.WOQUANTITY_FLD].ToString() != string.Empty)
                        {
                            orderDetail.WOQuantity = Convert.ToDecimal(detailRow[SO_SaleOrderDetailTable.WOQUANTITY_FLD]);
                        }
                        if (detailRow[SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD] != null && detailRow[SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD].ToString() != string.Empty)
                        {
                            orderDetail.ShipCompleted = Convert.ToBoolean(detailRow[SO_SaleOrderDetailTable.SHIPCOMPLETED_FLD]);
                        }

                        orderDetail.SO_DeliverySchedules.Add(new SO_DeliverySchedule
                                                                 {
                                                                     Line = 1,
                                                                     DeliveryQuantity = orderDetail.OrderQuantity,
                                                                     ScheduleDate = orderDetail.DueDate
                                                                 });
                        orderMaster.SO_SaleOrderDetails.Add(orderDetail);
                    }

                    dcPCS.SO_SaleOrderMasters.InsertOnSubmit(orderMaster);
                    dcPCS.SubmitChanges();
                    trans.Complete();
                    return orderMaster.SaleOrderMasterID;
                }
            }
            catch (SqlException ex)
            {
                if (ex.Errors.Count > 1)
                {
                    if (ex.Number == ErrorCode.SQLDUPLICATE_KEYCODE)
                        throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex);
                    throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
                }
                throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
            }
        }

        public bool CheckEditableSO(int pintSOMasterID)
        {
            const string methodName = This + ".CheckEditableSO()";

            var dstDetail = new DataSet();
            //Check if SaleOrder is released
            var dsSOCommitInventoryMaster = new SO_CommitInventoryMasterDS();
            //dstDetail.Tables[0]
            dstDetail.Tables.Add(
                dsSOCommitInventoryMaster.ListCommittedInventoryMasterBySaleOrderMasterID(pintSOMasterID).Tables[0].
                    Copy());
            if (dstDetail.Tables[0].Rows.Count > 0)
            {
                //SaleOrder is released
                throw new PCSBOException(ErrorCode.MESSAGE_CANNOT_DELETE_SALE_ORDER_LINE_BECAUSE_RELEASED, methodName,
                                         null);
            }
            //Check if SaleOrder is additional charge
            var dsSOAdditionCharge = new SO_AdditionChargeDS();
            //dstDetail.Tables[1]
            dstDetail.Tables.Add(dsSOAdditionCharge.GetAdditionalChargeBySOMasterID(pintSOMasterID).Tables[0].Copy());
            if (dstDetail.Tables[1].Rows.Count > 0)
            {
                //SaleOrder is additional charge
                throw new PCSBOException(ErrorCode.MESSAGE_CANNOT_DELETE_SALE_ORDER_BECAUSE_ADDITIONAL_CHARGE,
                                         methodName, null);
            }

            var dsDetail = new SO_SaleOrderDetailDS();
            //dstDetail.Tables[2]
            dstDetail.Tables.Add(dsDetail.List(pintSOMasterID).Tables[0].Copy());
            DataTable dtbDeliverySchedule;
            foreach (DataRow drow in dstDetail.Tables[2].Rows)
            {
                //Check if SaleOrder is delivery schedule
                var dsSODeliverySchedule = new SO_DeliveryScheduleDS();
                dtbDeliverySchedule =
                    dsSODeliverySchedule.GetDeliverySchedule(
                        int.Parse(drow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString())).Tables[0];
                if (dtbDeliverySchedule.Rows.Count > 0)
                {
                    //SaleOrder is delivery schedule
                    throw new PCSBOException(ErrorCode.MESSAGE_CANNOT_DELETE_SALE_ORDER_LINE_BECAUSE_DELIVERY_SCHEDULE,
                                             methodName, null);
                }
            }
            return true;
        }

        public void DeleteSaleOrder(int pintSOMasterID)
        {
            const string methodName = This + ".DeleteSaleOrder()";
            var dstDetail = new DataSet();
            //Check if SaleOrder is released
            var dsSOCommitInventoryMaster = new SO_CommitInventoryMasterDS();
            //dstDetail.Tables[0]
            dstDetail.Tables.Add(
                dsSOCommitInventoryMaster.ListCommittedInventoryMasterBySaleOrderMasterID(pintSOMasterID).Tables[0].
                    Copy());
            if (dstDetail.Tables[0].Rows.Count > 0)
            {
                //SaleOrder is released
                throw new PCSBOException(ErrorCode.MESSAGE_CANNOT_DELETE_SALE_ORDER_LINE_BECAUSE_RELEASED, methodName,
                                         null);
            }

            var dsDetail = new SO_SaleOrderDetailDS();
            //dstDetail.Tables[2]
            dstDetail.Tables.Add(dsDetail.List(pintSOMasterID).Tables[0].Copy());
            DataTable dtbDeliverySchedule;
            foreach (DataRow drow in dstDetail.Tables[1].Rows)
            {
                //Check if SaleOrder is delivery schedule
                var dsSODeliverySchedule = new SO_DeliveryScheduleDS();
                dtbDeliverySchedule =
                    dsSODeliverySchedule.GetDeliverySchedule(
                        int.Parse(drow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString())).Tables[0];
                if (dtbDeliverySchedule.Rows.Count > 0)
                    throw new PCSBOException(ErrorCode.MESSAGE_CANNOT_DELETE_SALE_ORDER_LINE_BECAUSE_DELIVERY_SCHEDULE,
                                             methodName, null);
                dsDetail.Delete(int.Parse(drow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString()));
            }
            var dsMaster = new SO_SaleOrderMasterDS();
            dsMaster.Delete(pintSOMasterID);
        }

        public DataSet ListDetailByMaster(int pintID)
        {
            var dsDetail = new SO_SaleOrderDetailDS();
            return dsDetail.List(pintID);
        }

        public SO_SaleOrderMaster GetSaleOrderMaster(int? iMasterID, string strTransNo)
        {
            using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
            {
                if (iMasterID != null && strTransNo != string.Empty)
                    return db.SO_SaleOrderMasters.Single(e => e.SaleOrderMasterID == iMasterID && e.Code == strTransNo);
                if (iMasterID != null)
                    return db.SO_SaleOrderMasters.Single(e => e.SaleOrderMasterID == iMasterID);
                return db.SO_SaleOrderMasters.Single(e => e.Code == strTransNo);
            }
        }

        public SO_SaleOrderMaster GetSaleOrderMaster(int iId)
        {
            SO_SaleOrderMaster obj;
            using (var trans = new TransactionScope())
            {
                using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    obj = db.SO_SaleOrderMasters.Single(e => e.SaleOrderMasterID == iId);
                }
                trans.Complete();
            }
            return obj;
        }

        public SO_SaleOrderDetail GetSaleOrderDetail(int iId)
        {
            SO_SaleOrderDetail obj;
            using (var trans = new TransactionScope())
            {
                using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    obj = db.SO_SaleOrderDetails.Single(e => e.SaleOrderDetailID == iId);
                }
                trans.Complete();
            }
            return obj;
        }

        public bool Delete(int iId)
        {
            using (var trans = new TransactionScope())
            {
                try
                {
                    using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                    {
                        db.ExecuteCommand("DELETE " + SO_SaleOrderDetailTable.TABLE_NAME + " WHERE  SaleOrderDetailID =" +
                                          iId);
                        db.SubmitChanges();
                    }
                }
                catch (Exception)
                {
                    Transaction.Current.Rollback();
                    throw;
                }
                finally
                {
                    trans.Complete();
                }
            }
            return true;
        }

        public DataSet GetSOBySOMasterID(int pintSOMasterID)
        {
            var dstData = new DataSet();
            var dsDetail = new SO_CommitInventoryDetailDS();
            dstData = dsDetail.GetSOBySOMasterID(pintSOMasterID);
            // get available quantity if any
            foreach (DataRow drowData in dstData.Tables[0].Rows)
            {
                int intMasterLocationID = 0;
                int intLocationID = 0;
                int intBinID = 0;
                int intProductID = 0;
                int intCCNID = 0;
                decimal decUMRate = 0;
                // master location id
                try
                {
                    if (drowData[SO_CommitInventoryDetailTable.MASTERLOCATIONID_FLD] != null &&
                        drowData[SO_CommitInventoryDetailTable.MASTERLOCATIONID_FLD].ToString() != string.Empty)
                        intMasterLocationID =
                            Convert.ToInt32(drowData[SO_CommitInventoryDetailTable.MASTERLOCATIONID_FLD]);
                    // location id
                    if (drowData[SO_CommitInventoryDetailTable.LOCATIONID_FLD] != null &&
                        drowData[SO_CommitInventoryDetailTable.LOCATIONID_FLD].ToString() != string.Empty)
                        intLocationID = Convert.ToInt32(drowData[SO_CommitInventoryDetailTable.LOCATIONID_FLD]);
                    if (drowData[SO_CommitInventoryDetailTable.BINID_FLD] != null &&
                        drowData[SO_CommitInventoryDetailTable.BINID_FLD].ToString() != string.Empty)
                        intBinID = Convert.ToInt32(drowData[SO_CommitInventoryDetailTable.BINID_FLD]);
                    if (drowData[SO_CommitInventoryDetailTable.CCNID_FLD] != null &&
                        drowData[SO_CommitInventoryDetailTable.CCNID_FLD].ToString() != string.Empty)
                        intCCNID = Convert.ToInt32(drowData[SO_SaleOrderMasterTable.CCNID_FLD]);

                    #region fill available quantity

                    if (drowData[SO_CommitInventoryDetailTable.PRODUCTID_FLD] != null &&
                        drowData[SO_CommitInventoryDetailTable.PRODUCTID_FLD].ToString() != string.Empty)
                        intProductID = Convert.ToInt32(drowData[SO_CommitInventoryDetailTable.PRODUCTID_FLD]);
                    if (drowData[SO_CommitInventoryDetailTable.UMRATE_FLD] != null &&
                        drowData[SO_CommitInventoryDetailTable.UMRATE_FLD].ToString() != string.Empty)
                        decUMRate = Convert.ToDecimal(drowData[SO_CommitInventoryDetailTable.UMRATE_FLD]);

                    // available quantity
                    decimal decAvailableQuantity = 0;
                    if (decUMRate <= 0)
                        decAvailableQuantity = new SODirectCommitmentBO().GetAvailableQuantity(intBinID, intLocationID,
                                                                                               intMasterLocationID,
                                                                                               intCCNID, intProductID,
                                                                                               drowData[
                                                                                                   SO_CommitInventoryDetailTable
                                                                                                       .LOT_FLD].
                                                                                                   ToString());
                    else
                        decAvailableQuantity =
                            new SODirectCommitmentBO().GetAvailableQuantity(intBinID, intLocationID, intMasterLocationID,
                                                                            intCCNID, intProductID,
                                                                            drowData[
                                                                                SO_CommitInventoryDetailTable.LOT_FLD].
                                                                                ToString())*decUMRate;
                    drowData[IV_MasLocCacheTable.OHQUANTITY_FLD] = decAvailableQuantity;

                    #endregion
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return dstData;
        }

        public DataSet GetSODetailDeliverySchedule(int pintMasterID)
        {
            var dstReturn = new DataSet();
            var dsSaleOrderDetail = new SO_SaleOrderDetailDS();
            dstReturn = dsSaleOrderDetail.GetSODetailDeliverySchedule(pintMasterID);
            return dstReturn;
        }

        public object GetExchangeRate(int pintCurrencyID, DateTime pdtmOrderDate)
        {
            var dsMST = new MST_ExchangeRateDS();
            return dsMST.GetExchangeRate(pintCurrencyID, pdtmOrderDate);
        }

        public int IsValidateData(string pstrValue, string pstrTable, string pstrField, string pstrCodition)
        {
            var dsMaster = new SO_SaleOrderMasterDS();
            return dsMaster.IsValidateData(pstrValue, pstrTable, pstrField, pstrCodition);
        }

        public DataRow GetDataRow(string pstrKeyField, string pstrValue, string pstrTable, string pstrField,
                                  string pstrCodition)
        {
            var dsMaster = new SO_SaleOrderMasterDS();
            return dsMaster.GetDataRow(pstrKeyField, pstrValue, pstrTable, pstrField, pstrCodition);
        }

        public DataRow LoadObjectVO(int pintMasterID)
        {
            var dsMaster = new SO_SaleOrderMasterDS();
            return dsMaster.LoadObjectVO(pintMasterID);
        }

        public int ImportNewSaleOrder(object pvoSOMaster, DataSet pdstSODetail, DataTable dtImportData,
                                      DataSet pdstGateData)
        {
            const string TEMP_QTY_COL_NAME = "TempQty";
            try
            {
                var dsMaster = new SO_SaleOrderMasterDS();
                int intSOMasterID = dsMaster.AddAndReturnID(pvoSOMaster);
                int intMonth = 0, intYear = 0;
                string strMMYYYY = dtImportData.Rows[0][0].ToString();
                const string DATE_SLASH = "/";
                try
                {
                    intMonth = int.Parse(strMMYYYY.Substring(0, strMMYYYY.IndexOf(DATE_SLASH)));
                    intYear = int.Parse(strMMYYYY.Substring(strMMYYYY.IndexOf(DATE_SLASH) + DATE_SLASH.Length));
                }
                catch
                {
                }

                if (pdstSODetail.Tables.Count > 0)
                {
                    int intIdx = 0;
                    foreach (DataRow objRow in pdstSODetail.Tables[0].Rows)
                    {
                        if (objRow.RowState == DataRowState.Deleted)
                        {
                            continue;
                        }
                        objRow[SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD] = intSOMasterID;

                        intIdx++;
                    }
                    var dsDetail = new SO_SaleOrderDetailDS();
                    dsDetail.UpdateDataSetForImport(pdstSODetail, intSOMasterID);

                    //get delivery schedules schema
                    var dsSODeli = new SO_DeliveryScheduleDS();
                    int intMasterID =
                        int.Parse(
                            pdstSODetail.Tables[SO_SaleOrderDetailTable.TABLE_NAME].Rows[0][
                                SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD].ToString());
                    DataSet dstDeli = dsSODeli.ListForImport(intMasterID);

                    //Add temp column for new quantity
                    var objCol = new DataColumn(TEMP_QTY_COL_NAME);
                    objCol.DataType = typeof (decimal);
                    objCol.DefaultValue = 0;
                    dstDeli.Tables[SO_DeliveryScheduleTable.TABLE_NAME].Columns.Add(objCol);

                    //insert delivery schedules
                    int intEndDataCol = dtImportData.Columns.Count - 1;
                    int intEndDataRow = dtImportData.Rows.Count;
                    intIdx = 0;
                    foreach (DataRow objRow in pdstSODetail.Tables[0].Rows)
                    {
                        int intLine = 0;
                        for (int i = INT_BEGIN_DATA_COL; i < intEndDataCol; i++)
                        {
                            for (int j = INT_BEGIN_DATA_ROW; j < intEndDataRow; j++)
                            {
                                if (
                                    dtImportData.Rows[j][INT_PARTS_NO_COL].ToString().Equals(
                                        objRow[SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD].ToString()))
                                {
                                    int intDay = int.Parse(dtImportData.Rows[INT_HEADER_ROW][i].ToString());
                                    int intHour = int.Parse(dtImportData.Rows[j][INT_TIME_COL].ToString());
                                    var dateDeli = new DateTime(intYear, intMonth, intDay, intHour, 0, 0);
                                    decimal dcmQty;
                                    if (dtImportData.Rows[j][i] == DBNull.Value)
                                    {
                                        continue;
                                    }
                                    else
                                    {
                                        intLine++;
                                        try
                                        {
                                            dcmQty = decimal.Parse(dtImportData.Rows[j][i].ToString());
                                        }
                                        catch
                                        {
                                            continue;
                                        }
                                    }

                                    //findout gate code
                                    string strGateCode = dtImportData.Rows[j][INT_CODE_COL].ToString().Trim();
                                    int intGateID = 0;
                                    if (strGateCode != string.Empty)
                                    {
                                        //lookup gate id
                                        DataRow[] arrGate =
                                            pdstGateData.Tables[0].Select(SO_GateTable.CODE_FLD + "='" + strGateCode +
                                                                          "'");
                                        if (arrGate.Length == 0)
                                        {
                                            //never occurs
                                        }
                                        else
                                        {
                                            intGateID = Convert.ToInt32(arrGate[0][SO_GateTable.GATEID_FLD]);
                                        }
                                    }

                                    //Check if this schedule exist, only update quantity
                                    string strFilter = SO_DeliveryScheduleTable.SALEORDERDETAILID_FLD + "=" +
                                                       objRow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD]
                                                       + " AND " + SO_DeliveryScheduleTable.SCHEDULEDATE_FLD + "='" +
                                                       dateDeli + "'";
                                    if (intGateID != 0)
                                    {
                                        strFilter += " AND " + SO_DeliveryScheduleTable.GATEID_FLD + "=" + intGateID;
                                    }
                                    else
                                    {
                                        strFilter += " AND " + SO_DeliveryScheduleTable.GATEID_FLD + " IS NULL";
                                    }

                                    DataRow[] arrSchRows =
                                        dstDeli.Tables[SO_DeliveryScheduleTable.TABLE_NAME].Select(strFilter);
                                    if (arrSchRows.Length > 0)
                                    {
                                        arrSchRows[0][TEMP_QTY_COL_NAME] =
                                            decimal.Parse(arrSchRows[0][TEMP_QTY_COL_NAME].ToString()) + dcmQty;
                                        continue;
                                    }

                                    DataRow drDeli = dstDeli.Tables[SO_DeliveryScheduleTable.TABLE_NAME].NewRow();
                                    drDeli[SO_DeliveryScheduleTable.SALEORDERDETAILID_FLD] =
                                        objRow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD];
                                    if (dtImportData.Rows[j][i] == DBNull.Value)
                                    {
                                        continue;
                                    }
                                    drDeli[SO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD] = dcmQty;
                                    drDeli[TEMP_QTY_COL_NAME] = dcmQty;
                                    drDeli[SO_DeliveryScheduleTable.SCHEDULEDATE_FLD] = dateDeli;
                                    drDeli[SO_DeliveryScheduleTable.LINE_FLD] = intLine;
                                    if (intGateID > 0)
                                    {
                                        drDeli[SO_DeliveryScheduleTable.GATEID_FLD] = intGateID;
                                            //objRow[SO_GateTable.GATEID_FLD];
                                    }
                                    dstDeli.Tables[SO_DeliveryScheduleTable.TABLE_NAME].Rows.Add(drDeli);
                                }
                            }
                        }
                        intIdx++;
                    }

                    //refine DelSch data
                    int intNewLine = 0;
                    DataRow[] arrRows = dstDeli.Tables[0].Select(string.Empty,
                                                                 SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD);
                    int intLastDetailID = -1;
                    int intCurrentProductID = -1;
                    for (int i = 0; i < arrRows.Length; i++)
                    {
                        int intDetailID = int.Parse(arrRows[i][SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString());

                        if (intDetailID != intLastDetailID)
                        {
                            //reset line
                            intNewLine = 0;
                            intLastDetailID = intDetailID;
                        }
                        if (decimal.Parse(arrRows[i][TEMP_QTY_COL_NAME].ToString()) == 0)
                        {
                            arrRows[i].Delete();
                        }
                        else
                        {
                            //update line
                            arrRows[i][SO_DeliveryScheduleTable.LINE_FLD] = ++intNewLine;
                            //only update qty
                            decimal dcmNewQTy = decimal.Parse(arrRows[i][TEMP_QTY_COL_NAME].ToString());
                            arrRows[i][SO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD] = dcmNewQTy;
                        }
                    }

                    dsSODeli.UpdateDataSet(dstDeli);
                    dstDeli.Tables[SO_DeliveryScheduleTable.TABLE_NAME].Columns.Remove(objCol);
                }
                return intSOMasterID;
            }
            catch (PCSException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet GetAllDeliveryLine(int pintSaleOrderMasterID)
        {
            try
            {
                var dsDelSch = new SO_DeliveryScheduleDS();
                return dsDelSch.GetDeliveryScheduleBySOMaster(pintSaleOrderMasterID);
            }
            catch (PCSException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ImportUpdateSaleOrder(object pvoSOMaster, DataSet pdstSODetail, DataTable dtImportData,
                                          DataSet pdstDelSchData, ref int pintErrorLine, DataSet pdstGateData)
        {
            const string TEMP_QTY_COL_NAME = "TempQty";
            const string methodName = This + ".ImportUpdateSaleOrder()";
            const string SUMCOMMITQUANTITY_FLD = "SUMCommitQuantity";
            //			try
            //			{
            int intMonth = 0, intYear = 0;
            string strMMYYYY = dtImportData.Rows[0][0].ToString();
            const string DATE_SLASH = "/";
            try
            {
                intMonth = int.Parse(strMMYYYY.Substring(0, strMMYYYY.IndexOf(DATE_SLASH)));
                intYear = int.Parse(strMMYYYY.Substring(strMMYYYY.IndexOf(DATE_SLASH) + DATE_SLASH.Length));
            }
            catch
            {
            }

            if (pdstSODetail.Tables.Count > 0)
            {
                int intRowIdx = 0;

                foreach (DataRow drowSODetail in pdstSODetail.Tables[0].Rows)
                {
                    if (drowSODetail.RowState == DataRowState.Deleted)
                    {
                        continue;
                    }
                    if (int.Parse(drowSODetail[SO_SaleOrderDetailTable.SALEORDERLINE_FLD].ToString()) == -1)
                    {
                        //delete all related 
                        int intDetailID =
                            int.Parse(drowSODetail[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString());
                        try
                        {
                            //online
                            //DeleteNotCommitedSaleOrderLine(intDetailID);

                            //offline
                            DataRow[] arrDeliverySchedule =
                                pdstDelSchData.Tables[0].Select(SO_DeliveryScheduleTable.SALEORDERDETAILID_FLD + "=" +
                                                                intDetailID);
                            foreach (DataRow drowDeliverySchedule in arrDeliverySchedule)
                            {
                                if (Convert.ToDecimal(drowDeliverySchedule[SUMCOMMITQUANTITY_FLD]) == 0)
                                {
                                    drowDeliverySchedule.Delete();
                                }
                                else
                                {
                                    pintErrorLine = intRowIdx + INT_BEGIN_DATA_ROW;
                                    throw new PCSDBException(ErrorCode.MESSAGE_IMPORT_ERROR_UPDATE_COMMITTED,
                                                             methodName, null);
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            //cannot delete commited schedule
                            pintErrorLine = intRowIdx + INT_BEGIN_DATA_ROW;
                            throw new PCSDBException(ErrorCode.MESSAGE_IMPORT_ERROR_UPDATE_COMMITTED, methodName, ex);
                        }
                        drowSODetail.Delete();
                        continue;
                    }
                    drowSODetail[SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD] =
                        ((SO_SaleOrderMasterVO) pvoSOMaster).SaleOrderMasterID;
                    intRowIdx++;
                }
                var dsDetail = new SO_SaleOrderDetailDS();

                //update deleted delivery schedule in dataset
                var dsDelSch = new SO_DeliveryScheduleDS();
                dsDelSch.UpdateDeletedRowInDataSet(pdstDelSchData,
                                                   ((SO_SaleOrderMasterVO) pvoSOMaster).SaleOrderMasterID);

                //update sale order detail dataset
                dsDetail.UpdateDataSetForImport(pdstSODetail, ((SO_SaleOrderMasterVO) pvoSOMaster).SaleOrderMasterID);

                //get delivery schedules schema
                var dsSODeli = new SO_DeliveryScheduleDS();
                int intMasterID =
                    int.Parse(
                        pdstSODetail.Tables[SO_SaleOrderDetailTable.TABLE_NAME].Rows[0][
                            SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD].ToString());
                //DataSet dstDeli = dsSODeli.ListForImport(intMasterID);

                //Add temp column for new quantity
                var objCol = new DataColumn(TEMP_QTY_COL_NAME);
                objCol.DataType = typeof (decimal);
                objCol.DefaultValue = 0;
                pdstDelSchData.Tables[SO_DeliveryScheduleTable.TABLE_NAME].Columns.Add(objCol);

                //insert delivery schedules
                int intEndDataCol = dtImportData.Columns.Count - 1;
                int intEndDataRow = dtImportData.Rows.Count;
                intRowIdx = -1;
                foreach (DataRow objRow in pdstSODetail.Tables[0].Rows)
                {
                    intRowIdx++;
                    int intLine = 0;
                    for (int i = INT_BEGIN_DATA_COL; i < intEndDataCol; i++)
                    {
                        for (int j = INT_BEGIN_DATA_ROW; j < intEndDataRow; j++)
                        {
                            if (
                                dtImportData.Rows[j][INT_PARTS_NO_COL].ToString().Equals(
                                    objRow[SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD].ToString()))
                            {
                                int intDay = int.Parse(dtImportData.Rows[INT_HEADER_ROW][i].ToString());
                                int intHour = int.Parse(dtImportData.Rows[j][INT_TIME_COL].ToString());
                                var dateDeli = new DateTime(intYear, intMonth, intDay, intHour, 0, 0);
                                decimal dcmQty;
                                if (dtImportData.Rows[j][i] == DBNull.Value)
                                {
                                    continue;
                                }
                                else
                                {
                                    intLine++;
                                    try
                                    {
                                        dcmQty = decimal.Parse(dtImportData.Rows[j][i].ToString());
                                        if (dcmQty <= 0)
                                        {
                                            continue;
                                        }
                                    }
                                    catch
                                    {
                                        continue;
                                    }
                                }

                                //findout gate code
                                string strGateCode = dtImportData.Rows[j][INT_CODE_COL].ToString().Trim();
                                int intGateID = 0;
                                if (strGateCode != string.Empty)
                                {
                                    //lookup gate id
                                    DataRow[] arrGate =
                                        pdstGateData.Tables[0].Select(SO_GateTable.CODE_FLD + "='" + strGateCode + "'");
                                    if (arrGate.Length == 0)
                                    {
                                        //never occurs
                                    }
                                    else
                                    {
                                        intGateID = Convert.ToInt32(arrGate[0][SO_GateTable.GATEID_FLD]);
                                    }
                                }

                                //Check if this schedule exist, only update quantity
                                string strFilter = SO_DeliveryScheduleTable.SALEORDERDETAILID_FLD + "=" +
                                                   objRow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD]
                                                   + " AND " + SO_DeliveryScheduleTable.SCHEDULEDATE_FLD + "='" +
                                                   dateDeli + "'";

                                if (intGateID > 0)
                                {
                                    strFilter += " AND " + SO_DeliveryScheduleTable.GATEID_FLD + "=" + intGateID;
                                }
                                else
                                {
                                    strFilter += " AND " + SO_DeliveryScheduleTable.GATEID_FLD + " IS NULL";
                                }

                                DataRow[] arrSchRows =
                                    pdstDelSchData.Tables[SO_DeliveryScheduleTable.TABLE_NAME].Select(strFilter);
                                if (arrSchRows.Length > 0)
                                {
                                    arrSchRows[0][TEMP_QTY_COL_NAME] =
                                        decimal.Parse(arrSchRows[0][TEMP_QTY_COL_NAME].ToString()) + dcmQty;
                                    if (intGateID > 0)
                                    {
                                        arrSchRows[0][SO_DeliveryScheduleTable.GATEID_FLD] = intGateID;
                                    }
                                    else
                                    {
                                        arrSchRows[0][SO_DeliveryScheduleTable.GATEID_FLD] = DBNull.Value;
                                    }
                                    continue;
                                }

                                DataRow drDeli = pdstDelSchData.Tables[SO_DeliveryScheduleTable.TABLE_NAME].NewRow();
                                drDeli[TEMP_QTY_COL_NAME] = dcmQty;
                                drDeli[SO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD] = dcmQty;
                                drDeli[SO_DeliveryScheduleTable.SALEORDERDETAILID_FLD] =
                                    objRow[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD];
                                drDeli[SO_DeliveryScheduleTable.SCHEDULEDATE_FLD] = dateDeli;
                                drDeli[SO_DeliveryScheduleTable.LINE_FLD] = intLine;
                                if (intGateID > 0)
                                {
                                    drDeli[SO_DeliveryScheduleTable.GATEID_FLD] = intGateID;
                                }
                                else
                                {
                                    drDeli[SO_DeliveryScheduleTable.GATEID_FLD] = DBNull.Value;
                                }
                                pdstDelSchData.Tables[SO_DeliveryScheduleTable.TABLE_NAME].Rows.Add(drDeli);
                            }
                        }
                    }
                }

                //refine DelSch data
                int intNewLine = 0;
                DataRow[] arrRows = pdstDelSchData.Tables[0].Select(string.Empty,
                                                                    SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD);
                int intLastDetailID = -1;
                int intCurrentProductID = -1;
                var dsCommit = new SO_CommitInventoryDetailDS();

                intRowIdx = -1;
                for (int i = 0; i < arrRows.Length; i++)
                {
                    int intDetailID = int.Parse(arrRows[i][SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString());

                    if (intDetailID != intLastDetailID)
                    {
                        //reset line
                        intNewLine = 0;
                        intLastDetailID = intDetailID;
                        intRowIdx++;

                        //get new product id
                        DataRow[] arrDetails =
                            pdstSODetail.Tables[SO_SaleOrderDetailTable.TABLE_NAME].Select(
                                SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + "=" + intLastDetailID);
                        //check if not found? not needed
                        intCurrentProductID = int.Parse(arrDetails[0][SO_SaleOrderDetailTable.PRODUCTID_FLD].ToString());
                    }
                    if (decimal.Parse(arrRows[i][TEMP_QTY_COL_NAME].ToString()) == 0)
                    {
                        //Not exist in excel file
                        int intMasterSOID = ((SO_SaleOrderMasterVO) pvoSOMaster).SaleOrderMasterID;
                        int intDeliID = int.Parse(arrRows[i][SO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD].ToString());
                        decimal dcmRemainQty = dsCommit.GetRemainQuantity(intMasterSOID, intDeliID, intCurrentProductID);
                        decimal dcmOldQty =
                            decimal.Parse(arrRows[i][SO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD].ToString());
                        if (dcmRemainQty != dcmOldQty)
                        {
                            pintErrorLine = intRowIdx + INT_BEGIN_DATA_ROW;
                            throw new PCSDBException(ErrorCode.MESSAGE_IMPORT_ERROR_UPDATE_COMMITTED, methodName, null);
                            //cannot delete because this schedule is commited, what we will to do here ???
                        }
                        else
                        {
                            arrRows[i].Delete();
                        }
                    }
                    else
                    {
                        if (arrRows[i].RowState != DataRowState.Added)
                        {
                            //update line
                            arrRows[i][SO_DeliveryScheduleTable.LINE_FLD] = ++intNewLine;
                            //only update qty
                            //check for total comitted
                            int intMasterSOID = ((SO_SaleOrderMasterVO) pvoSOMaster).SaleOrderMasterID;
                            int intDeliID =
                                int.Parse(arrRows[i][SO_DeliveryScheduleTable.DELIVERYSCHEDULEID_FLD].ToString());
                            //decimal dcmRemainQty = dsCommit.GetRemainQuantity(intMasterSOID, intDeliID,intCurrentProductID);
                            decimal dcmCommittedQty = Convert.ToDecimal(arrRows[i][SUMCOMMITQUANTITY_FLD]);
                            //decimal dcmOldQty = decimal.Parse(arrRows[i][SO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD].ToString());
                            decimal dcmNewQTy = decimal.Parse(arrRows[i][TEMP_QTY_COL_NAME].ToString());
                            if (dcmCommittedQty > dcmNewQTy)
                            {
                                //Cannot update quantity less than commited
                                pintErrorLine = intRowIdx + INT_BEGIN_DATA_ROW;
                                throw new PCSDBException(ErrorCode.MESSAGE_IMPORT_ERROR_UPDATE_COMMITTED, methodName,
                                                         null);
                            }
                            else
                            {
                                arrRows[i][SO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD] = dcmNewQTy;
                            }
                        }
                        else
                        {
                            //update line
                            arrRows[i][SO_DeliveryScheduleTable.LINE_FLD] = ++intNewLine;
                            arrRows[i][SO_DeliveryScheduleTable.DELIVERYQUANTITY_FLD] = arrRows[i][TEMP_QTY_COL_NAME];
                        }
                    }
                }

                pdstDelSchData.Tables[SO_DeliveryScheduleTable.TABLE_NAME].Columns.Remove(objCol);
                dsSODeli.UpdateInsertedRowInDataSet(pdstDelSchData,
                                                    ((SO_SaleOrderMasterVO) pvoSOMaster).SaleOrderMasterID);
            }
            pintErrorLine = -1;
        }

        public void ImportUpdateSaleOrder(object pvoSOMaster, DataSet pdstSODetail, DataTable dtImportData,
                                          DataSet pdstDelSchData, ref int pintErrorLine)
        {
            const string TEMP_QTY_COL_NAME = "TempQty";
            const string methodName = This + ".ImportUpdateSaleOrder()";
            const string SUMCOMMITQUANTITY_FLD = "SUMCommitQuantity";
            int intMonth = 0, intYear = 0;
            string strMMYYYY = dtImportData.Rows[0][0].ToString();
            const string DATE_SLASH = "/";
            try
            {
                intMonth = int.Parse(strMMYYYY.Substring(0, strMMYYYY.IndexOf(DATE_SLASH)));
                intYear = int.Parse(strMMYYYY.Substring(strMMYYYY.IndexOf(DATE_SLASH) + DATE_SLASH.Length));
            }
            catch
            {
            }

            if (pdstSODetail.Tables.Count > 0)
            {
                int intRowIdx = 0;

                foreach (DataRow drowSODetail in pdstSODetail.Tables[0].Rows)
                {
                    if (drowSODetail.RowState == DataRowState.Deleted)
                    {
                        continue;
                    }
                    if (int.Parse(drowSODetail[SO_SaleOrderDetailTable.SALEORDERLINE_FLD].ToString()) == -1)
                    {
                        //delete all related 
                        int intDetailID =
                            int.Parse(drowSODetail[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString());
                        try
                        {
                            //online
                            //DeleteNotCommitedSaleOrderLine(intDetailID);

                            //offline
                            DataRow[] arrDeliverySchedule =
                                pdstDelSchData.Tables[0].Select(SO_DeliveryScheduleTable.SALEORDERDETAILID_FLD + "=" +
                                                                intDetailID);
                            foreach (DataRow drowDeliverySchedule in arrDeliverySchedule)
                            {
                                if (Convert.ToDecimal(drowDeliverySchedule[SUMCOMMITQUANTITY_FLD]) == 0)
                                {
                                    drowDeliverySchedule.Delete();
                                }
                                else
                                {
                                    pintErrorLine = intRowIdx + INT_BEGIN_DATA_ROW;
                                    throw new PCSDBException(ErrorCode.MESSAGE_IMPORT_ERROR_UPDATE_COMMITTED,
                                                             methodName, null);
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            //cannot delete commited schedule
                            pintErrorLine = intRowIdx + INT_BEGIN_DATA_ROW;
                            throw new PCSDBException(ErrorCode.MESSAGE_IMPORT_ERROR_UPDATE_COMMITTED, methodName, ex);
                        }
                        drowSODetail.Delete();
                        continue;
                    }
                    drowSODetail[SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD] =
                        ((SO_SaleOrderMasterVO) pvoSOMaster).SaleOrderMasterID;
                    intRowIdx++;
                }
                var dsDetail = new SO_SaleOrderDetailDS();

                //update deleted delivery schedule in dataset
                var dsDelSch = new SO_DeliveryScheduleDS();
                dsDelSch.UpdateDeletedRowInDataSet(pdstDelSchData,
                                                   ((SO_SaleOrderMasterVO) pvoSOMaster).SaleOrderMasterID);

                //update sale order detail dataset
                dsDetail.UpdateDataSetForImport(pdstSODetail, ((SO_SaleOrderMasterVO) pvoSOMaster).SaleOrderMasterID);
            }
            pintErrorLine = -1;
        }

        /// <summary>
        /// Get customer item reference by partyid and productid
        /// </summary>
        /// <param name="pintProductID"></param>
        /// <param name="pintCustomerID"></param>
        /// <returns></returns>
        public object GetItemCustomerRef(int pintProductID, int pintCustomerID)
        {
            return new SO_CustomerItemRefDetailDS().GetObjectVO(pintProductID, pintCustomerID);
        }

        /// <summary>
        /// CheckSORelease by sale order detail
        /// </summary>
        /// <param name="pintSaleOrderDetailID"></param>
        /// <returns></returns>
        /// <author>Trada</author>
        /// <date>Friday,April 7 2006</date>
        public bool CheckSOReleased(int pintSaleOrderMasterID, int pintSaleOrderDetailID)
        {
            var dsDetail = new SO_CommitInventoryDetailDS();
            return dsDetail.IsCommited(pintSaleOrderMasterID, pintSaleOrderDetailID);
        }

        /// <summary>
        /// CheckFGoodsCode
        /// </summary>
        /// <param name="pStrParaFGoodsCode"></param>
        /// <returns></returns>
        /// <author>CanhNv</author>
        /// <date>Tuesday,September,18 2007</date>
        public DataSet CheckFGoodsCode(string[] pStrParaFGoodsCode, int[] pintSaleOrderdetailID)
        {
            var dsSaleOrderDetail = new SO_SaleOrderDetailDS();
            DataSet dstSODetail = dsSaleOrderDetail.ListDetailByFGoodsCode(pStrParaFGoodsCode, pintSaleOrderdetailID);
            return dstSODetail;
        }

        public decimal GetUnitPriceDefualt(int pProductID, int pCustomerID)
        {
            return (new SO_SaleOrderDetailDS()).GetUnitPriceDefualt(pProductID, pCustomerID);
        }

        public int ImportNewSaleOrder(object pvoSOMaster, DataSet pdstSODetail, DataTable dtImportData)
        {
            var dsMaster = new SO_SaleOrderMasterDS();
            int intSOMasterID = dsMaster.AddAndReturnID(pvoSOMaster);

            if (pdstSODetail.Tables.Count > 0)
            {
                foreach (DataRow objRow in pdstSODetail.Tables[0].Rows)
                {
                    if (objRow.RowState == DataRowState.Deleted)
                    {
                        continue;
                    }
                    objRow[SO_SaleOrderDetailTable.SALEORDERMASTERID_FLD] = intSOMasterID;
                }
                var dsDetail = new SO_SaleOrderDetailDS();
                dsDetail.UpdateDataSetForImport(pdstSODetail, intSOMasterID);
            }
            return intSOMasterID;
        }

        public DataSet ListScheduleForImport(int pintMasterID)
        {
            var dsSODeli = new SO_DeliveryScheduleDS();
            return dsSODeli.ListForImport(pintMasterID);
        }

        public void UpdateScheduleForImport(DataSet pdstData)
        {
            var dsSchedule = new SO_DeliveryScheduleDS();
            dsSchedule.UpdateDataSet(pdstData);
        }

        public void UpdateInsertedRowInDataSet(DataSet pdstDelSchData, int pintMasterID)
        {
            var dsSchedule = new SO_DeliveryScheduleDS();
            dsSchedule.UpdateInsertedRowInDataSet(pdstDelSchData, pintMasterID);
        }

        public DataTable GetRemainQuantity(int pintMasterID)
        {
            var dsCommit = new SO_CommitInventoryDetailDS();
            return dsCommit.GetRemainQuantity(pintMasterID);
        }

        public DataSet GetListDetail(int printMasterID, int printCustomerRefMasterID)
        {
            var objSODetailDS = new SO_SaleOrderDetailDS();
            return objSODetailDS.GetListDetail(printMasterID, printCustomerRefMasterID);
        }

        public void UpdateUnitPrice(DataSet pdstSODetail)
        {
            if (pdstSODetail != null)
            {
                if (pdstSODetail.Tables.Count > 0)
                {
                    var dsDetail = new SO_SaleOrderDetailDS();
                    dsDetail.UpdateDataSet(pdstSODetail);
                }
            }
        }

        public DataTable GetProduceRequestData(int pintSaleMasterID)
        {
            var dsMaster = new SO_SaleOrderMasterDS();
            return dsMaster.GetProduceRequestData(pintSaleMasterID);
        }

        #region // HACK: DuongNA 2005-10-20

        private const int INT_HEADER_ROW = 1;
        private const int INT_BEGIN_DATA_ROW = 3;

        private const int INT_PARTS_NO_COL = 1;
        private const int INT_TIME_COL = 4;
        private const int INT_CODE_COL = 5;
        private const int INT_BEGIN_DATA_COL = 6;

        //[AutoComplete(true)]
        public int ImportNewMappingData(DataTable dtImpData, int intPartyID, int intCCNID, int intMaxLine,
                                        DataSet dstMappingData, DataSet dstGateData)
        {
            //add new row for gate in detail table
            if (!dstMappingData.Tables[0].Columns.Contains(SO_DeliveryScheduleTable.GATEID_FLD))
            {
                var dcolGateID = new DataColumn(SO_DeliveryScheduleTable.GATEID_FLD, typeof (int));
                dstMappingData.Tables[0].Columns.Add(dcolGateID);
            }
            int intResult = 0;
            int intMaxID = 0;
            dstMappingData.Tables[0].DefaultView.Sort = SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD;
            try
            {
                intMaxID =
                    int.Parse(
                        dstMappingData.Tables[0].Rows[dstMappingData.Tables[0].Rows.Count - 1][
                            SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString());
            }
            catch
            {
                intMaxID = 0;
            }
            dstMappingData.Tables[0].DefaultView.Sort = string.Empty;

            //			try
            //			{
            //walk through data	
            var dsSaleOrderDetail = new SO_SaleOrderDetailDS();
            var dsMasterRef = new SO_CustomerItemRefMasterDS();
            var voMasterRef = (SO_CustomerItemRefMasterVO) dsMasterRef.GetObjectVO(intPartyID, intCCNID);

            var dsDetailRef = new SO_CustomerItemRefDetailDS();
            var dsProduct = new ITM_ProductDS();

            for (int i = INT_BEGIN_DATA_ROW; i < dtImpData.Rows.Count; i++)
            {
                //findout gate code
                string strGateCode = dtImpData.Rows[i][INT_CODE_COL].ToString().Trim();
                int intGateID = 0;
                if (strGateCode != string.Empty)
                {
                    //lookup gate id
                    DataRow[] arrGate = dstGateData.Tables[0].Select(SO_GateTable.CODE_FLD + "='" + strGateCode + "'");
                    if (arrGate.Length == 0)
                    {
                        intResult = -i;
                        //negative mean that error is caused by gate code not found
                        //positive mean that error is caused by item mapping not exist
                        break;
                    }
                    else
                    {
                        intGateID = Convert.ToInt32(arrGate[0][SO_GateTable.GATEID_FLD]);
                    }
                }

                //findout Customer Code
                string strCustomerCode = dtImpData.Rows[i][INT_PARTS_NO_COL].ToString();
                string strCustomerModel = dtImpData.Rows[i][INT_BEGIN_DATA_COL - 1].ToString();

                //find out total quantity at last column
                decimal dcmOrderQty = decimal.Parse(dtImpData.Rows[i][dtImpData.Columns.Count - 1].ToString());

                //check if this item existed, update quantity only
                DataRow[] arrRows =
                    dstMappingData.Tables[0].Select(SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD + "='" +
                                                    strCustomerCode + "'");
                //+ " and " + SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD + "= '" + strCustomerModel + "'");
                if (arrRows.Length > 0)
                {
                    arrRows[0][SO_SaleOrderDetailTable.ORDERQUANTITY_FLD] =
                        decimal.Parse(arrRows[0][SO_SaleOrderDetailTable.ORDERQUANTITY_FLD].ToString()) + dcmOrderQty;
                    continue;
                }

                if (dcmOrderQty <= 0)
                {
                    continue;
                }

                //findout master_ref_id
                int intMasterRefID = voMasterRef.CustomerItemRefMasterID;
                if (intMasterRefID == 0)
                {
                    intResult = i;
                    break;
                }

                //find out product_id
                var voDetailRef = (SO_CustomerItemRefDetailVO) dsDetailRef.GetObjectVO(intMasterRefID, strCustomerCode);
                    //, strCustomerModel);

                int intProductID = voDetailRef.ProductID;
                if (intProductID == 0)
                {
                    intResult = i;
                    break;
                }
                ITM_Product voProduct = new ProductItemInfoBO().GetProductInfo(intProductID);

                //New row
                DataRow dr = dstMappingData.Tables[0].NewRow();

                var boUtils = new UtilsBO();
                //fill row
                dr[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD] = dcmOrderQty;
                dr[SO_SaleOrderDetailTable.PRODUCTID_FLD] = intProductID;
                dr[SO_SaleOrderDetailTable.SELLINGUMID_FLD] = voDetailRef.UnitOfMeasureID;
                dr[SO_SaleOrderDetailTable.UNITPRICE_FLD] = voDetailRef.UnitPrice;
                dr[SO_SaleOrderDetailTable.TOTALAMOUNT_FLD] = voDetailRef.UnitPrice*dcmOrderQty;
                dr[SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD] = dtImpData.Rows[i][INT_PARTS_NO_COL];
                dr[SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD] = voDetailRef.CustomerItemModel;
                dr[SO_SaleOrderDetailTable.UMRATE_FLD] = boUtils.GetUMRate(voProduct.StockUMID,
                                                                           voDetailRef.UnitOfMeasureID);
                dr[SO_SaleOrderDetailTable.SALEORDERLINE_FLD] = ++intMaxLine;
                dr[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD] = ++intMaxID;
                dr[SO_SaleOrderDetailTable.STOCKUMID_FLD] = voProduct.StockUMID;
                if (intGateID > 0)
                {
                    dr[SO_DeliveryScheduleTable.GATEID_FLD] = intGateID; //lookup for gateid
                }
                dstMappingData.Tables[0].Rows.Add(dr);
            }
            return intResult;
        }

        //[AutoComplete(true)]
        public int ImportUpdateMappingData(DataTable dtImpData, int intPartyID, int intCCNID, int intMaxLine,
                                           DataSet dstMappingData, DataSet dstGateData)
        {
            int intResult = 0;
            const string TEMP_QTY_COL_NAME = "TempQty";

            //Add new column for temp qty
            var objCol = new DataColumn(TEMP_QTY_COL_NAME);
            objCol.DataType = typeof (Decimal);
            objCol.DefaultValue = 0;
            dstMappingData.Tables[0].Columns.Add(objCol);
            //add new row for gate in detail table
            if (!dstMappingData.Tables[0].Columns.Contains(SO_DeliveryScheduleTable.GATEID_FLD))
            {
                var dcolGateID = new DataColumn(SO_DeliveryScheduleTable.GATEID_FLD, typeof (int));
                dstMappingData.Tables[0].Columns.Add(dcolGateID);
            }

            int intMaxID = 0;
            dstMappingData.Tables[0].DefaultView.Sort = SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD;
            try
            {
                intMaxID =
                    int.Parse(
                        dstMappingData.Tables[0].Rows[dstMappingData.Tables[0].Rows.Count - 1][
                            SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD].ToString());
            }
            catch
            {
                intMaxID = 0;
            }
            dstMappingData.Tables[0].DefaultView.Sort = string.Empty;

            //			try
            //			{

            //walk through data	
            var dsSaleOrderDetail = new SO_SaleOrderDetailDS();
            var dsMasterRef = new SO_CustomerItemRefMasterDS();
            var voMasterRef = (SO_CustomerItemRefMasterVO) dsMasterRef.GetObjectVO(intPartyID, intCCNID);
            var dsProduct = new ITM_ProductDS();

            for (int i = INT_BEGIN_DATA_ROW; i < dtImpData.Rows.Count; i++)
            {
                //findout gate code
                string strGateCode = dtImpData.Rows[i][INT_CODE_COL].ToString().Trim();
                int intGateID = 0;
                if (strGateCode != string.Empty)
                {
                    //lookup gate id
                    DataRow[] arrGate = dstGateData.Tables[0].Select(SO_GateTable.CODE_FLD + "='" + strGateCode + "'");
                    if (arrGate.Length == 0)
                    {
                        intResult = -i;
                        break;
                    }
                    else
                    {
                        intGateID = Convert.ToInt32(arrGate[0][SO_GateTable.GATEID_FLD]);
                    }
                }
                //findout Customer Code
                string strCustomerCode = dtImpData.Rows[i][INT_PARTS_NO_COL].ToString();
                //find out total quantity at last column
                decimal dcmOrderQty = int.Parse(dtImpData.Rows[i][dtImpData.Columns.Count - 1].ToString());

                //check if this item existed, update quantity only
                DataRow[] arrRows =
                    dstMappingData.Tables[0].Select(SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD + "='" +
                                                    strCustomerCode + "'");
                if (arrRows.Length > 0)
                {
                    arrRows[0][TEMP_QTY_COL_NAME] = decimal.Parse(arrRows[0][TEMP_QTY_COL_NAME].ToString()) +
                                                    dcmOrderQty;
                    if (intGateID > 0)
                    {
                        arrRows[0][SO_DeliveryScheduleTable.GATEID_FLD] = intGateID;
                    }
                    else
                    {
                        arrRows[0][SO_DeliveryScheduleTable.GATEID_FLD] = DBNull.Value;
                    }
                    continue;
                }
                if (dcmOrderQty <= 0)
                {
                    continue;
                }

                //else

                //findout master_ref_id
                int intMasterRefID = voMasterRef.CustomerItemRefMasterID;
                if (intMasterRefID == 0)
                {
                    intResult = i;
                    break;
                }

                //find out product_id
                var dsDetailRef = new SO_CustomerItemRefDetailDS();
                var voDetailRef = (SO_CustomerItemRefDetailVO) dsDetailRef.GetObjectVO(intMasterRefID, strCustomerCode);
                int intProductID = voDetailRef.ProductID;
                if (intProductID == 0)
                {
                    intResult = i;
                    break;
                }
                ITM_Product voProduct = new ProductItemInfoBO().GetProductInfo(intProductID);

                //New row
                DataRow dr = dstMappingData.Tables[0].NewRow();

                var boUtils = new UtilsBO();
                //fill row
                dr[TEMP_QTY_COL_NAME] = dcmOrderQty;
                dr[SO_SaleOrderDetailTable.ORDERQUANTITY_FLD] = dcmOrderQty;
                dr[SO_SaleOrderDetailTable.PRODUCTID_FLD] = intProductID;
                dr[SO_SaleOrderDetailTable.SELLINGUMID_FLD] = voDetailRef.UnitOfMeasureID;
                dr[SO_SaleOrderDetailTable.UNITPRICE_FLD] = voDetailRef.UnitPrice;
                dr[SO_SaleOrderDetailTable.TOTALAMOUNT_FLD] = voDetailRef.UnitPrice*dcmOrderQty;
                dr[SO_SaleOrderDetailTable.ITEMCUSTOMERCODE_FLD] = dtImpData.Rows[i][INT_PARTS_NO_COL];
                dr[SO_SaleOrderDetailTable.ITEMCUSTOMERREVISION_FLD] = voDetailRef.CustomerItemModel;
                    //dtImpData.Rows[i][INT_PARTS_NAME_COL];
                dr[SO_SaleOrderDetailTable.UMRATE_FLD] = boUtils.GetUMRate(voProduct.StockUMID,
                                                                           voDetailRef.UnitOfMeasureID);
                dr[SO_SaleOrderDetailTable.SALEORDERLINE_FLD] = ++intMaxLine;
                dr[SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD] = ++intMaxID;
                dr[SO_SaleOrderDetailTable.STOCKUMID_FLD] = voProduct.StockUMID;
                if (intGateID > 0)
                {
                    dr[SO_DeliveryScheduleTable.GATEID_FLD] = intGateID;
                }
                dr[TEMP_QTY_COL_NAME] = dcmOrderQty;
                dstMappingData.Tables[0].Rows.Add(dr);
            }
            if (intResult != 0)
            {
                dstMappingData.Tables[0].Columns.Remove(objCol);
                return intResult;
            }
            //refine data, with correct line
            int intLine = 1;
            for (int i = 0; i < dstMappingData.Tables[0].Rows.Count; i++)
            {
                if (int.Parse(dstMappingData.Tables[0].Rows[i][TEMP_QTY_COL_NAME].ToString()) == 0)
                {
                    //not found in excel file
                    //dstMappingData.Tables[0].Rows[i].Delete();
                    //mark as deleted
                    dstMappingData.Tables[0].Rows[i][SO_SaleOrderDetailTable.SALEORDERLINE_FLD] = -1;
                }
                else
                {
                    //Update Line
                    dstMappingData.Tables[0].Rows[i][SO_SaleOrderDetailTable.SALEORDERLINE_FLD] = intLine;
                    //Update quantity
                    dstMappingData.Tables[0].Rows[i][SO_SaleOrderDetailTable.ORDERQUANTITY_FLD] =
                        dstMappingData.Tables[0].Rows[i][TEMP_QTY_COL_NAME];
                    intLine++;
                }
            }

            dstMappingData.Tables[0].Columns.Remove(objCol);
            return intResult;
        }

        #endregion // END: DuongNA 2005-10-20
    }
}